package com.example.dw.util;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class DBSqlUtils {

    /**
     * 执行更新（INSERT, UPDATE, DELETE）
     */
    public static int executeUpdate(DataSource dataSource,String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            setParameters(pstmt, params);

            return pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException("执行更新失败: " + sql, e);
        } finally {
            closeStatement(pstmt);
            closeConnection(conn);
        }
    }

    /**
     * 检查表是否存在
     */
    public static boolean tableExists(DataSource dataSource,String tableName) {
        Connection conn = null;
        ResultSet rs = null;

        try {
            conn = dataSource.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();

            // 根据数据库类型调整catalog和schema
            String catalog = conn.getCatalog();
            String schema = conn.getSchema();

            rs = metaData.getTables(catalog, schema, tableName, new String[]{"TABLE"});
            return rs.next();

        } catch (SQLException e) {
            throw new RuntimeException("检查表是否存在失败: " + tableName, e);
        } finally {
            closeResultSet(rs);
            closeConnection(conn);
        }
    }

    /**
     * 设置PreparedStatement参数
     */
    private static void setParameters(PreparedStatement pstmt, Object... params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
        }
    }

    /**
     * 创建表
     */
    public boolean createTable(DataSource dataSource,String createTableSql) {
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.execute(createTableSql);
            return true;

        } catch (SQLException e) {
            throw new RuntimeException("创建表失败: " + createTableSql, e);
        } finally {
            closeStatement(stmt);
            closeConnection(conn);
        }
    }

    /**
     * 查询表的字段信息
     */
    public static List<Map<String, Object>> getTableColumns(DataSource dataSource,String tableName) {
        Connection conn = null;
        ResultSet rs = null;
        List<Map<String, Object>> columns = new ArrayList<>();

        try {
            conn = dataSource.getConnection();
            DatabaseMetaData metaData = conn.getMetaData();

            String catalog = conn.getCatalog();
            String schema = conn.getSchema();

            rs = metaData.getColumns(catalog, schema, tableName, "%");

            while (rs.next()) {
                Map<String, Object> column = new LinkedHashMap<>();
                column.put("columnName", rs.getString("COLUMN_NAME"));
                column.put("dataType", rs.getString("TYPE_NAME"));
                column.put("columnSize", rs.getInt("COLUMN_SIZE"));
                column.put("decimalDigits", rs.getInt("DECIMAL_DIGITS"));
                column.put("isNullable", rs.getInt("NULLABLE"));
                column.put("remarks", rs.getString("REMARKS"));
                column.put("columnDefault", rs.getString("COLUMN_DEF"));
                column.put("autoIncrement", rs.getString("IS_AUTOINCREMENT"));
                columns.add(column);
            }

        } catch (SQLException e) {
            throw new RuntimeException("查询表字段失败: " + tableName, e);
        } finally {
            closeResultSet(rs);
            closeConnection(conn);
        }

        return columns;
    }

    /**
     * 执行查询 - 返回List<Map>
     */
    public List<Map<String, Object>> executeQuery(DataSource dataSource,String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Map<String, Object>> resultList = new ArrayList<>();

        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            setParameters(pstmt, params);

            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                resultList.add(row);
            }

        } catch (SQLException e) {
            throw new RuntimeException("执行查询失败: " + sql, e);
        } finally {
            closeResultSet(rs);
            closeStatement(pstmt);
            closeConnection(conn);
        }

        return resultList;
    }

    /**
     * 批量执行更新
     */
    public static int[] executeBatch(DataSource dataSource,String sql, List<Object[]> paramsList) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);

            for (Object[] params : paramsList) {
                setParameters(pstmt, params);
                pstmt.addBatch();
            }

            return pstmt.executeBatch();

        } catch (SQLException e) {
            throw new RuntimeException("批量执行失败: " + sql, e);
        } finally {
            closeStatement(pstmt);
            closeConnection(conn);
        }
    }

    /**
     * 关闭语句
     */
    private static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭结果集
     */
    private static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     */
    private static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
